Release 10.1A: OpenEdge Data Management:
SQL Development

Using the SQLSCHEMA utility

The SQLSCHEMA utility is a command-line utility that writes SQL database schema components to an output file selectively. You can capture table definitions including table constraints, views, stored procedures including related privileges, and triggers. At the command line you specify which components to dump.

Use the following syntax for the SQLSCHEMA utility:

Syntax

Operating system	Syntax
UNIX Windows	sqlschema -u user_name [ -a password ] [ -t [ owner_name.]table_name1              [,owner_name.]table_name2, ... ] [-t [ owner_name.]view_name1      [,owner_name.]view_name2, ... ] [ -p [ owner_name.] procedure_name, ... ] [ -T [ owner_name.] trigger_name, ... ] [ -g [ owner_name.]table_name, ... ] [ -s [ owner_name.]table_name, ... ] [ -o output_file_name ] database_name

The SQLSCHEMA utility cannot write definitions for Progress 4GL tables. Table definitions include the database area name for the table, derived from a scan of the area and objects. When SQLSCHEMA writes a table definition, it does not automatically write associated triggers, synonyms, or privileges. These must be explicitly specified on the command line. Capturing database schema requires privileges to access the requested components.

Example 5–33 directs the SQLSCHEMA utility to write table definitions and trigger information. The output goes to the screen since no output_file_name is specified. Since the user_name and password are not specified, SQLSCHEMA will prompt the user for these values.

Example 5–33: SQLSCHEMA utility for writing object definitions

sqlschema -t tucker.customers,tucker.products -T tucker.customers,tucker.products progress:T:thunder:4077:salesdb

Example 5–34 directs the SQLSCHEMA utility to write table definitions to an output file named salesdbschema.dfsql.

Example 5–34: SQLSCHEMA for writing object definitions to output file

sqlschema -u tucker -a sulky -t %.cust%,%.invent%,%.sales% -o salesdbschema.dfsql progress:T:thunder:4077:salesdb

Note: Each output file created by the SQLSCHEMA utility records character set information about the contents of the file. When you use SQLSCHEMA to dump schema information from a database, the schema is written in Unicode UTF-8.

For more information on SQL utilities and database administration, see OpenEdge Data Management: Database Administration .